using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Reflection;

using phorm.database.core;
using phorm.database.mssql;
using phorm.database.mysql;
using phorm.database.oracle;

namespace phorm.model.fields
{
    /// <summary>
    /// Many-to-many column
    /// </summary>
    /// <typeparam name="T">A specific DatabaseRecord that is the relative to this ManyToMany column</typeparam>
    public class ManyToMany<T> : BaseManyToMany, IJoinableColumn, IIgnorableColumn
    {
        /// <summary>
        /// The relative DatabaseRecord type
        /// </summary>
        private Type t;

        /// <summary>
        /// Constructor establishes model object, type, and pivot table of this column. Also, populates column into model object.
        /// </summary>
        /// <param name="obj">The model object to which this column belongs</param>
        public ManyToMany(DatabaseRecord obj)
        {
            this._model = obj;
            this.t = typeof(T);
            this.pivot_table = "pivot_"+this.model.GetType().Name.ToLower()+"_"+t.Name.ToLower();
            populateColumn();
        }

        /// <summary>
        /// Definition of the all function required by abstract extension of BaseManyToMany.
        /// </summary>
        /// <returns>List of DatabaseRecords that are related to this column's model object</returns>
        public override List<DatabaseRecord> all()
        {
            List<DatabaseRecord> list = new List<DatabaseRecord>();
            if (this.model is MSSQLRecord)
            {
                MSSQLDatabase.connection();
                SqlDataReader reader = MSSQLDatabase.query(phorm.database.mssql.CRUD.allManyToMany(this));
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        DatabaseRecord record = Activator.CreateInstance(this._model.GetType()) as DatabaseRecord;
                        foreach (KeyValuePair<string, DatabaseColumn> column in this._model.columns)
                        {
                            if (!(column.Value is IIgnorableColumn))
                            {
                                record.columns[column.Key].value = reader[column.Key].ToString();
                            }
                        }
                        list.Add(record);
                    }
                }
                MSSQLDatabase.finish();
            }
            else if (this.model is MySQLRecord)
            {
                MySQLDatabase.connection();
                MySQLDatabase.query(phorm.database.mssql.CRUD.allManyToMany(this));
                MySQLDatabase.finish();
            }
            else if (this.model is OracleRecord)
            {
                OracleDatabase.connection();
                OracleDatabase.query(phorm.database.mssql.CRUD.allManyToMany(this));
                OracleDatabase.finish();
            }

            return list;
        }

        /// <summary>
        /// Method for adding an object as a related object to this column's model object.
        /// </summary>
        /// <param name="obj">The object to add</param>
        public void add(T obj)
        {
            if (this.model.primary_key.value == null)
                this.model.save();

            if (obj is DatabaseRecord)
            {
                DatabaseRecord record = obj as DatabaseRecord;
                if (record.primary_key.value == null)
                    record.save();


                if (this.model is MSSQLRecord)
                {
                    MSSQLDatabase.connection();
                    MSSQLDatabase.nonQuery(phorm.database.mssql.CRUD.addManyToMany(this, obj as DatabaseRecord));
                    MSSQLDatabase.finish();
                }
                else if (this.model is MySQLRecord)
                    Console.WriteLine("MySQL: " + this.model.GetType());
                else if (this.model is OracleRecord)
                    Console.WriteLine("Oracle" + this.model.GetType());
            }
        }

        /// <summary>
        /// Method for removing a relationship to this column's model object.
        /// </summary>
        /// <param name="obj">The object to remove</param>
        public void remove(T obj)
        {
            if (this.model.primary_key.value == null)
                this.model.save();

            if (obj is DatabaseRecord)
            {
                DatabaseRecord record = obj as DatabaseRecord;
                if (record.primary_key.value == null)
                    record.save();


                if (this.model is MSSQLRecord)
                {
                    MSSQLDatabase.connection();
                    MSSQLDatabase.nonQuery(phorm.database.mssql.CRUD.removeManyToMany(this, obj as DatabaseRecord));
                    MSSQLDatabase.finish();
                }
                else if (this.model is MySQLRecord)
                    Console.WriteLine("MySQL: " + this.model.GetType());
                else if (this.model is OracleRecord)
                    Console.WriteLine("Oracle" + this.model.GetType());
            }
        }
    }
}
